﻿-- =============================================
-- Author:		<Author,XFL>
-- Edit date: <Create Date,2012 02 01>
-- Description:	<Description, [人资管理]-[业绩绩效考核]-[销售业绩]
--	将原来的存储过程进行了优化,并且提速效果很明显
--	同时,部门查询是 使用了部门编号查询 BMBH >
-- =============================================
CREATE PROCEDURE [dbo].[proc_Employee_Jixiao_TongjiOne_BackUp]
	(
		@companyId int,
		@departmentId int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@EName nvarchar(50),
		@StartIndex int,
		@EndIndex int
	)
AS
begin

			declare @bmbh_T  varchar(50)
			set @bmbh_T = (select bmbh  FROM department WHERE id=@departmentId)		
			declare @indextable table(id int identity(1,1),nid int)
			insert into @indextable(nid) Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T;

		declare @list_table_1 table(
		id int identity(1,1)
		,Eid int
		,username nvarchar(50)
		,Company nvarchar(50) /*公司名称*/
		,Department nvarchar(50) /*部门名称*/
		,EName nvarchar(50) /*部门名称*/
		,Order_Count int	
		,Order_FullPrice Decimal
		)


		;WITH list As 
		(
		select 
		id,username,
		(Select CompanyName From Company Where Id=e.CompanyId) As Company,
		(Select DName From Department Where Id=e.DepartmentId) As Department,
		EName,
		(Select Count([Order].Id) From [Order] Where [Order].salesmanaccount=e.UserName and [Order].salesmanaccount <>''  and [Order].StateId >=2 and Convert(nchar(10),[Order].TradeDate,120) between @StartDate and @EndDate) As Order_Count,
		ISNULL((Select Sum([Order].ShiShouPrice) From [Order] Where [Order].salesmanaccount=e.UserName and [Order].salesmanaccount <>''  and [Order].StateId >=2 and Convert(nchar(10),[Order].TradeDate,120) between @StartDate and @EndDate),0) As Order_FullPrice
		From Employee e
		Where (@CompanyId=0 Or e.CompanyId=@companyId) 
		and (@DepartmentId=0 Or DepartmentId in (select c.nid from @indextable c)) 
		and (@EName is null Or e.EName like '%'+@EName+'%') and state <> 5
		)


		insert into @list_table_1(
		Eid,username,
		company 
		,department 
		,ename 
		,Order_Count 
		,Order_FullPrice 
		) 
		select 
		id,username,
		company 
		,department 
		,ename 
		,Order_Count 
		,Order_FullPrice 
		From list  
		order by Order_FullPrice desc ,Order_Count desc, ename;
		-- 上面完成 list 表
--			Select * from @list_table_1 

		declare @A int,@B int,@C Decimal
		set @A =(select count(0) from @list_table_1 )
		set @B =(select sum(Order_Count) from @list_table_1 ) 
		set @C=(select sum(Order_FullPrice) from @list_table_1 ) 

	declare @list_table_2 table(
		id int identity(1,1)
		,Row int
		,Eid int
		,username nvarchar(50)
		,Company nvarchar(50) /*公司名称*/
		,Department nvarchar(50) /*部门名称*/
		,EName nvarchar(50) /*部门名称*/
		,Order_Count int	
		,Order_FullPrice Decimal

		)

		;WITH list_return as (
		Select ROW_NUMBER() OVER (ORDER BY Id) AS Row
		,eid as id,username
		,company 
		,department 
		,ename 
		,Order_Count 
		,Order_FullPrice 
		from @list_table_1
		)

		insert into @list_table_2(
		Row
		,Eid 
		,username
		,Company 
		,Department
		,EName
		,Order_Count 
		,Order_FullPrice 

		)
		Select 
		Row
		,id
		,username
		,company 
		,department 
		,ename 
		,Order_Count 
		,Order_FullPrice 

		From list_return 
		Where Row between @StartIndex and @EndIndex

		ORDER BY Row;





		select  
		Row	
		,Eid as id  
		,username 
		,Company 
		,Department 
		,EName 
		,Order_Count 
		,Order_FullPrice 
		,@A as Count_Sum 
		,@B as Order_Count_Sum 
		,@C as Order_FullPrice_Sum 
		,ISNULL((Select sum([project].SalePrice-[project].CostPrice) From [project] Where [project].salesmanaccount=E.UserName and [project].salesmanaccount <>'' and Convert(nchar(10),[project].CreateDate,120) between @StartDate and @EndDate),0) As Maoli
		,ISNULL((select top 1 [Employee].state from [Employee] Where [Employee].UserName=[E].UserName ),0) as State
		from @list_table_2 e



end
	RETURN
